All Records From Table A - All Records From Table B - Join Alike Records

I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

Re: All Records From Table A - All Records From Table B - Join Alike Records

The UNION clause can be used to join the results of two queries.

http://dev.mysql.com/doc/refman/5.1/en/union.html

The basic syntax is..

CREATE TABLE test1 (id int, something varchar(50));
CREATE TABLE test2 (id int, something varchar(50));

INSERT INTO test1 (id, something) VALUES (1, 'hello'),(2, 'world');
INSERT INTO test2 (id, something) VALUES (1, 'foo'),(2, 'bar');

SELECT something, id FROM test1
UNION
SELECT something, id FROM test2;

kevinjbowman wrote:
> I am by no means a SQl Jedi as will be apparent by my question, but I
> can usually figure out a select statement on my own. I have one today
> though that really has me stumped. I am working in MySQlL 5.
>
> In My first select statement I get all my records from Table B
> SELECT
> `table_A`.`ITEM`,
> `table_A`.`DECSCRIPTION`,
> `table_A`.`UM`,
> `table_A`.`PHASE`,
> `table_B`.`Qty`,
> `table_B`.`Calc` as calculated
> FROM
> `table_A`
> Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
>
> In my second statement I get my records that match in this case phase
> 401 in Table B and all my Table A records for phase 401.
>
> SELECT
> `table_A`.`ITEM`,
> `table_A`.`DECSCRIPTION`,
> `table_A`.`UM`,
> `table_A`.`PHASE`,
> `table_B`.`Qty`,
> `table_B`.`Calc` as calculated
> FROM
> `table_A`
> Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
> Where
> table_A.PHASE In ('401' )
>
> Now I need to combine the Data of both recordsets. I need EVERYTHING in
> Table B, but I also need All Table A records that match the phase
> selection....
>
> Can I write this one query or do I need to use a Temp table?

Re: All Records From Table A - All Records From Table B - Join AlikeRecords

kevinjbowman wrote:
> I am by no means a SQl Jedi as will be apparent by my question, but I
> can usually figure out a select statement on my own. I have one today
> though that really has me stumped. I am working in MySQlL 5.
>
> In My first select statement I get all my records from Table B
> SELECT
> `table_A`.`ITEM`,
> `table_A`.`DECSCRIPTION`,
> `table_A`.`UM`,
> `table_A`.`PHASE`,
> `table_B`.`Qty`,
> `table_B`.`Calc` as calculated
> FROM
> `table_A`
> Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
>
> In my second statement I get my records that match in this case phase
> 401 in Table B and all my Table A records for phase 401.
>
> SELECT
> `table_A`.`ITEM`,
> `table_A`.`DECSCRIPTION`,
> `table_A`.`UM`,
> `table_A`.`PHASE`,
> `table_B`.`Qty`,
> `table_B`.`Calc` as calculated
> FROM
> `table_A`
> Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
> Where
> table_A.PHASE In ('401' )
>
> Now I need to combine the Data of both recordsets. I need EVERYTHING in
> Table B, but I also need All Table A records that match the phase
> selection....
>
> Can I write this one query or do I need to use a Temp table?
>


You can use UNION but you have to make sure your field counts match.