DCJones DCJones - 4 months ago 14
MySQL Question

MySQL Select all rows from table 1 and all rows from table2 where

I have a table (Table1) with 80 rows, I have another table (Table2) with 20 rows.

Each table has a column which contains the same data and its this column I am trying to use to produce a query that returns all the rows in table1 and all the rows in table2, even if table2 does not have a match in table1.

So far I have:

SELECT
Table2.SeqNo,
Table2.SeqHeader,
Table2.SeqText,
Table1.UniqueID,
Table1.Room,
Table1.`Status`
FROM
Table1
Inner Join Table2 ON Table2.SeqID = Table1.SeqID
WHERE Table1.UniqueID = (a value)


I have tried many different way of writing this query but I am now stuck.

Any help would be great.

Edit:

I now have:

SELECT
Table2.SeqNo,
Table2.SeqHeader,
Table2.SeqText,
Table1.UniqueID,
Table1.Room,
Table1.`Status`
FROM
Table1
LEFT OUTER JOIN Table2 ON Table2.SeqID = Table1.SeqID
WHERE Table1.UniqueID = (a value)


When I run the query the result only reutuns rows where "Table1.UniqueID = (a value)". What I need is All the rows in Table2 and all the rows in table1.

Expected result:


| SeqNo | SeqHeader | SeqText | UniqueID |-- Room-- |-- Status--|
+---------+--------------+-------------+---------------+------------+-----------+
|- Data - |-----Data-----|----Data- |--Data---- |--- Data----|--- Data-- |
|- Data - |-----Data-----|----Data- |--Data---- |--- Data----|--- Data-- |
|- Data - |-----Data-----|----Data- |-No Data-- |-No Data-- |-No Data- |
|- Data - |-----Data-----|----Data- |-No Data-- |-No Data-- |-No Data- |
|- Data - |-----Data-----|----Data- |-No Data-- |-No Data-- |-No Data- |
|- Data - |-----Data-----|----Data- |-No Data-- |-No Data-- |-No Data- |


This is the best I can do as I don't have the data as in live data because I can't get the query to work as yet.

I hope this helps.

Answer

Now that I see your expected result, it looks like your join is reversed. Try

SELECT
Table2.SeqNo,
Table2.SeqHeader,
Table2.SeqText,
Table1.UniqueID,
Table1.Room,
Table1.`Status`
FROM
Table2
LEFT OUTER JOIN Table1 ON Table2.SeqID = Table1.SeqID
WHERE Table2.UniqueID = (a value)