What could be wrong

[I-AM-OBODO]

Hello all.

 

I am trying to get values from two tables but i am getting the wrong output regardless of the type of join i use.

The two query have some similar columns/rows. I want to fetch only the specified columns/rows with their unique data but my query is giving me on a column the value of just another column. For instance if a description column in  table A has a value of INVOICE and on table B the description is RECEIPT all my query show is either INVOICE or RECEIPT in all the columns regardless of whether the description is receipt or not and also i want all the fields that is not on the other table to be blank and not replicate columns eg. description. image refuse to show when attached and wouldn't allow me to use the image link on the editor, said i am not allowed.

SELECT table1.trans_ref, table1.description, table1.date_paid, table1.recurring, table1.amt, table1.bill_code, table2.trans_ref, table2.description, table2.amt_deposited, table2.deposit_date FROM table1 RIGHT JOIN table2 ON table1.username = table2.username

ps: would want amount and deposit amount to be on same column and also use a where username = username.

 

or maybe i am not doing the right thing? is it possible to a select on two tables without using a JOIN? so that you can echo only the needed values in a loop.

 

thanks

Edited March 14, 2015 by Mr-Chidi

[requinix]

The problem is how you're fetching the data in your code. You can't use an associative array, like $row["description"], because the row has multiple columns named "trans_ref" and "description".

 

You could use another fetch method that lets you refer to each value by column number, which sucks because then you have to keep track of what column has what values, or you can use aliases in your query:

SELECT
	table1.trans_ref AS table1_trans_ref,
	table1.description AS table1_description,
	table1.date_paid AS table1_date_paid,
	table1.recurring AS table1_recurring,
	table1.amt AS table1_amt,
	table1.bill_code AS table1_bill_code,
	table2.trans_ref AS table2_trans_ref,
	table2.description AS table2_description,
	table2.amt_deposited AS table2_amt_deposited,
	table2.deposit_date AS table2_deposit_date
FROM table1
RIGHT JOIN table2 ON table1.username = table2.username

(named all the columns with "tableN_" for consistency)

 

And I think you should be using a LEFT JOIN, if either at all. Ask yourself what you want to happen (a) if there are rows in table1 with no matching rows in table2, and (b) if there are rows in table2 with no matching rows in table1. How you answer determines whether it's LEFT, RIGHT, or INNER JOIN.

Edited March 14, 2015 by requinix

[mac_gyver]

something tells me that the rows between these two tables (using table names like table1, table2 doesn't provide useful context) are not related to each other using any sort of foreign key, but are two different types of data (that perhaps should all be in one table) that the OP wants to retrieve with the data for each username (which should actually be a userid) together in the result set. if so, you need to use a UNION query.

[I-AM-OBODO]

something tells me that the rows between these two tables (using table names like table1, table2 doesn't provide useful context) are not related to each other using any sort of foreign key, but are two different types of data (that perhaps should all be in one table) that the OP wants to retrieve with the data for each username (which should actually be a userid) together in the result set. if so, you need to use a UNION query.

yeah. u are right. the only relationship with both tables is the username. a table may have more info on it than the other.

 

thanks