CODEWITHSUNDEEP
phpmysqljoinmerge
hungrykoala
hungrykoala

Reputation: 1083

combine two tables without any similar values (MySQL + PHP)

I found this query that can combine two tables with different number of rows and without any related fields:

SELECT a.*, b.* FROM table1 a, table2 b;

Now my problem is that in my case I cannot change the value of the FROM.

Example I have this Query:

SELECT * FROM table1;

I cannot change that to:

SELECT * FROM table2;

At most, I can only add statements after FROM table_name LEFT JOIN somedefaulttable ON somedefaulttable2.id = somedefaulttable .id_c. (I forgot to add this important detail, I can only add custom queries after this line, I cannot remove this predetermined LEFT JOIN as this is system generated) The reason for this is because I am restricted by the CRM I am using, and I cannot edit the default select value but I can add additional custom queries after the said statement.

Now what my main problem here is that I need to combine a different table in my Query and I cannot use join as they don't have any common values and their number of rows are also different, I also tried using JOIN before to no avail, and thus decided to try using a different approach such as merging the two tables.

This is the link to my previous question where I was using JOIN to achieve my goal of combining the tables. In this link, you can see that I want to combine Table A and Table 4 but I cannot do so with JOIN as they have a different number of rows and that I am limited in changing my current Query to fit the posted answer.

Table A.

id | name | deleted | amount | due_date   | status
1  | a    | 0       | 10     | 2016-07-18 | Unpaid
2  | b    | 0       | 20     | 2016-07-19 | Unpaid
3  | c    | 0       | 15     | 2016-07-18 | Unpaid

Table B

id | name   | due_date   | status
1  | a      |            | Unpaid
2  | b      |            | Unpaid
3  | c      |            | Unpaid
4  | d      | 2016-07-19 | Unpaid

Table C

id | table_d_id | table_a_id
1  | 1          | 1
2  | 2          | 2
3  | 3          | 3

Table D

id | 
1
2
3

Upvotes: 0

Views: 150

Answers (2)

Franck Dang
Franck Dang

Reputation: 5

i have created a link of one to many between the tables with your example and number of rows you inserted you should try this:

     SELECT TableC.*
     FROM TableB, TableD INNER JOIN (TableA INNER JOIN TableC ON TableA.ID =       TableC.table_a_id) ON TableD.ID = TableC.table_d_id;

this will bring all the records of table C

Upvotes: 0

Marcus Belz
Marcus Belz

Reputation: 191

Try this for (inner) joining two tables without a JOIN Statement. 

SELECT a.x, a.y, b.x, b.y FROM  table1 a, table2 b WHERE a.x = b.x

What I do not understand is, how different numbers of rows affect the requested solution.

Upvotes: 1

Related Questions