1

Say I have tbl_company with these

cid-------   cons_no--------   company_name  -------   phone ------   address 
1----------  q1----------------aple------------------------22222-------newyork
2 ---------- q2----------------samsung----------------11111----------Ohio
3 ---------- q3 -------------- nokia ---------------------333333--------LONDON 



tbl_company_track with these
id-------cid-------cons_no--------   branch_location  -------   date_opened 
1------- 1-----------q1---------------stamford------------------2010
2------- 1-----------q1---------------Kingsway------------------2011
3------- 1-----------q1---------------Cornerstone---------------2014
4------- 2-----------q2---------------Madison-------------------2001
5------- 3-----------q3---------------lewiston------------------2015

I want to be able to display on html

Cons_no-----company_name------ phone branck_location------date_opened 

I want both table to correspond in respect to the cid and cons_no

so I can have record for one company:

 Cons_no          company_name          phone    branch    date opened
    q1                Aple              22222    stamford    2010
                                                 kingsway    2011
                                                 Cornerstone 2014

I HOPE I WAS ABLE TO COMMUNICATE MY PROBLEM WELL ENOUGH?

1
  • 1
    Have you try anything at least?
    – oNare
    Sep 7 '15 at 12:55
1

To answer this, I did the following (you can check out the SQLFiddle here).

CREATE TABLE Company
(
  cid INT,
  cons_no VARCHAR(2),
  company_name VARCHAR(20),
  phone VARCHAR(15),
  address VARCHAR(20)
);

INSERT INTO Company 
VALUES 
(1, 'q1', 'Apple', '222222', 'New York'), 
(2, 'q2', 'Samsung', '11111', 'Ohio'), 
(3, 'q3', 'Nokia', '333333', 'London');



CREATE TABLE Company_Track
(
  id INT,
  cid INT,
  cons_no VARCHAR(2),
  branch_location VARCHAR(20),
  date_opened INT
);

INSERT INTO Company_Track 
VALUES
(1, 1, 'q1', 'Stamford', 2010),
(2, 1, 'q1', 'Kingsway', 2011),
(3, 1, 'q1', 'Cornerstone', 2014),
(4, 2, 'q2', 'Madison', 2001),
(5, 3, 'q3', 'Lewiston', 2015);

And then ran the query

SELECT  c.company_name, c.cons_no, t.branch_location
    FROM  Company c
    INNER JOIN  Company_Track t ON c.cid = t.cid;

Which gives the result you want

company_name    cons_no branch_location
Apple           q1      Stamford
Apple           q1      Kingsway
Apple           q1      Cornerstone
Samsung         q2      Madison
Nokia           q3      Lewiston
2
  • Thanks but its meant to display only data for a single cons_no (where cons_no is seen as the logged in user) For example: Apple tracks it's company records on the database.... So I want it to display only data applicable to Apple and when samsung signs in it picks us only data applicable to samsung..... Sep 7 '15 at 18:08
  • 1
    Check out the SQLFiddle here. With large tables, you'd want to investigate how indexing would help speed up your queries. You might also want to consider RI (Referential Integrity) as a further safeguard for your data consistency. Sep 8 '15 at 6:59
0
AND c.company_name = 'Apple'

It is up to you to somehow translate the userid of the login to 'Apple'.

0

THANKS PALS, I have been able to figure it out. I did something like this and somewhere inside the page I fetched data from $sql_1 to enable me echo from that table:

$sql = "SELECT *

    FROM tbl_company
    WHERE cons_no = '$cons'";

$sql_1= "SELECT *

    FROM tbl_company_track
    WHERE cons_no = '$cons'";

$result = dbQuery($sql);

$result_1 = dbQuery($sql_1);

$no = dbNumRows($result);

if($no == 1){

while($data = dbFetchAssoc($result)) {

extract($data);

?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.