Displaying more than one Query result

[macleodjb]

Sorry for the novice question but i'm not that good at sql. I'm coding in php and i want to query my database, and display the data. Problem is this...i have two tables that i am querying from but i want to display the results as a whole. Here's an example

First database table (jobinfo)
Second Database table (jobstatus)

I want to pull ('job name', 'job number', 'install date') for every record in 'jobinfo'.

I want to pull ('job started', 'job in progress', 'job completed') for every record in 'jobstatus'.

Now display all these columns together for each job ordered by the 'install date'

So my ultimate question is How do i combine the two queries and display it as one on my webpage, while having it ordered by the install date?

Thanks in advance.

[nikkiH]

JOIN the two on the corresponding key.
One table should have a primary key, such as jobid. The other should have a foreign key that refers to it, so you can join them up.

[Fumigator]

You need a column in the jobstatus table that points to a row in jobinfo table. Is this the "job started" column? Is that a job number that points to a job number in jobinfo?

If that is the case, you can join the two tables together. A join is a query that gives you one result row for every match found between two tables. If two rows match the same value, you will have two rows in the query results.

Here's an example:

//I assign the two tables to an alias (the "as" keyword) so you can tell which table the column belongs in
SELECT ji.jobname, ji.jobnumber, ji.installdate
FROM jobinfo as ji
JOIN jobstatus as js
ON ji.jobnumber = js.jobstarted
ORDER BY ji.installdate

[macleodjb]

jobnumber is present in both tables, and there would only be one instance of jobnumber in each table. I understand what you (fumigator) showed me but the only thing i still have in question is how i call my columns out of the other table (ji). Is this correct?


SELECT ji.jobname, ji.jobnumber, ji.installdate
FROM jobinfo as ji
JOIN jobstatus as js
ON ji.jobnumber = js.jobnumber
SELECT
FROM jobstatus
ORDER BY ji.installdate

or is this correct

SELECT ji.jobname, ji.jobnumber, ji.installdate, js.jobstarted, js.jobinprogress, js.jobcompleted
FROM jobinfo as ji
JOIN jobstatus as js
ON ji.jobnumber = js.jobstarted
ORDER BY ji.installdate



Thanks for the help

[Fumigator]

The second one is correct (just change the JOIN ON condition to ji.jobnumber = js.jobnumber).

Feel free to try out different things and see for yourself how they work :)

[macleodjb]

Ok i gotcha now. Thanks for the help.